const express = require('express');
const router = express.Router();
const pool = require('../db');
const authMiddleware = require('../middleware/auth');
const { createLog } = require('../log');

// GET /api/counties
// If page & limit provided returns { counties: [...], total }
// Otherwise returns { counties: [...] } (all)
router.get('/counties', authMiddleware, async (req, res) => {
  try {
    // log page open
    createLog(req, { message: '打开区县列表', tp: 'view' });

    // detect whether pagination params were provided
    const hasPage = typeof req.query.page !== 'undefined';
    const hasLimit = typeof req.query.limit !== 'undefined';

    // if neither provided, we'll return all records
    let page = null;
    let limit = null;
    let offset = null;

    if (hasPage || hasLimit) {
      // when pagination is requested, default missing values: page -> 1, limit -> 10
      page = Math.max(1, parseInt(req.query.page, 10) || 1);
      limit = Math.max(1, parseInt(req.query.limit, 10) || 10);
      offset = (page - 1) * limit;
    }

    const allowedSorts = {
      county_name: 'c.county_name',
      county_code: 'c.county_code',
      city: 'ct.city_name',
      id: 'c.id'
    };
    const sortBy = typeof req.query.sort_by === 'string' ? req.query.sort_by : null;
    let sortOrder = typeof req.query.sort_order === 'string' ? req.query.sort_order.toLowerCase() : null;
    if (sortOrder !== 'asc' && sortOrder !== 'desc') sortOrder = null;

    let orderClause = 'c.id DESC';
    if (sortBy && allowedSorts[sortBy]) {
      orderClause = `${allowedSorts[sortBy]} ${sortOrder === 'asc' ? 'ASC' : 'DESC'}`;
    }

    let sql = `SELECT c.id, c.county_name, c.city, c.county_code, c.remark, ct.city_name FROM atb_county c LEFT JOIN atb_city ct ON c.city = ct.id ORDER BY ${orderClause}`;
    
    if (page !== null && limit !== null) {
      sql += ' LIMIT ? OFFSET ?';
      const [rows] = await pool.query(sql, [limit, offset]);
      const [countRows] = await pool.query('SELECT COUNT(*) AS total FROM atb_county');
      const total = (countRows && countRows[0] && countRows[0].total) ? countRows[0].total : 0;
     
      return res.json({ counties: rows, total });
    } else {
      const [rows] = await pool.query(sql);
     
      return res.json({ counties: rows });
    }
    
  } catch (err) {
    console.error('Failed to fetch counties:', err);
    res.status(500).json({ message: 'Server error' });
  }
});

// POST /api/counties
router.post('/counties', authMiddleware, async (req, res) => {
  try {
    const { county_name, city, county_code, remark } = req.body;
    if (!county_name) return res.status(400).json({ message: 'county_name required' });

    // uniqueness: same county_name within the same city
    const [existing] = await pool.query('SELECT id FROM atb_county WHERE county_name = ? AND (city = ? OR (city IS NULL AND ? IS NULL))', [county_name, city || null, city || null]);
    if (existing && existing.length > 0) return res.status(409).json({ message: '区县名称在该市州已存在', field: 'county_name' });

    const [result] = await pool.query('INSERT INTO atb_county (county_name, city, county_code, remark) VALUES (?, ?, ?, ?)', [county_name, city || null, county_code || null, remark || null]);
    const insertId = result.insertId;
    const [rows] = await pool.query('SELECT c.id, c.county_name, c.city, c.county_code, c.remark, ct.city_name FROM atb_county c LEFT JOIN atb_city ct ON c.city = ct.id WHERE c.id = ?', [insertId]);

    // log creation
    createLog(req, { message: `新增区县 ${county_name}`, tp: 'create', obj_json: JSON.stringify(rows[0] || { id: insertId, county_name }) });

    res.status(201).json({ county: rows[0] });
  } catch (err) {
    console.error('Failed to create county:', err);
    res.status(500).json({ message: 'Server error' });
  }
});

// PUT /api/counties/:id
router.put('/counties/:id', authMiddleware, async (req, res) => {
  try {
    const id = parseInt(req.params.id, 10);
    if (isNaN(id)) return res.status(400).json({ message: 'Invalid county id' });

    const { county_name, city, county_code, remark } = req.body;
    const fields = [];
    const params = [];

    if (typeof county_name !== 'undefined') {
      // uniqueness check within the same city
      const [rows] = await pool.query('SELECT id FROM atb_county WHERE county_name = ? AND (city = ? OR (city IS NULL AND ? IS NULL)) AND id != ?', [county_name, city || null, city || null, id]);
      if (rows && rows.length > 0) return res.status(409).json({ message: '区县名称在该市州已存在', field: 'county_name' });
      fields.push('county_name = ?'); params.push(county_name);
    }
    if (typeof city !== 'undefined') { fields.push('city = ?'); params.push(city); }
    if (typeof county_code !== 'undefined') { fields.push('county_code = ?'); params.push(county_code); }
    if (typeof remark !== 'undefined') { fields.push('remark = ?'); params.push(remark); }

    if (fields.length === 0) return res.status(400).json({ message: 'No fields to update' });

    params.push(id);
    const sql = `UPDATE atb_county SET ${fields.join(', ')} WHERE id = ?`;
    await pool.query(sql, params);

    const [updated] = await pool.query('SELECT c.id, c.county_name, c.city, c.county_code, c.remark, ct.city_name FROM atb_county c LEFT JOIN atb_city ct ON c.city = ct.id WHERE c.id = ?', [id]);

    // log update
    createLog(req, { message: `编辑区县 id=${id}`, tp: 'update', obj_json: JSON.stringify(updated[0] || { id }) });

    res.json({ county: updated[0] });
  } catch (err) {
    console.error('Failed to update county:', err);
    res.status(500).json({ message: 'Server error' });
  }
});

// DELETE /api/counties/:id
router.delete('/counties/:id', authMiddleware, async (req, res) => {
  try {
    const id = parseInt(req.params.id, 10);
    if (isNaN(id)) return res.status(400).json({ message: 'Invalid county id' });

    await pool.query('DELETE FROM atb_county WHERE id = ?', [id]);

    // log delete
    createLog(req, { message: `删除区县 id=${id}`, tp: 'delete', obj_json: JSON.stringify({ id }) });

    res.json({ ok: true });
  } catch (err) {
    console.error('Failed to delete county:', err);
    res.status(500).json({ message: 'Server error' });
  }
});

module.exports = router;
